
//
//  DataBaseHandle.m
//  Music
//
//  Created by dllo on 16/6/13.
//  Copyright © 2016年 dllo. All rights reserved.
//

#import "DataBaseHandle.h"
#import <sqlite3.h>
#import "Collection.h"
@implementation DataBaseHandle
//创建一个数据库对象
static sqlite3 *db;

+(instancetype)shareDataBaseHandle
{
    // static 修饰词作用：1 在静态区开空间，程序退出时，内存才释放  2 只初始化一次
    static DataBaseHandle *databaseHandle = nil;
    
    if (databaseHandle == nil) {
        databaseHandle = [[DataBaseHandle alloc] init];
    }
    
    return databaseHandle;
}

//打开数据库
-(void)open
{
    NSString *docPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
    NSString *sqlPath = [docPath stringByAppendingPathComponent:@"students.sqlite"];
    //NSLog(@"%@",docPath);
    //如果数据库已经打开，直接返回，不运行下面代码
    if (db != nil) {
        NSLog(@"数据库已经打开");
        return;
    }
    
    //核心API ： sqlite3_open 函数
    int result = sqlite3_open(sqlPath.UTF8String, &db);
    
    //根据函数返回值，判断操作是否正确
    if (result == SQLITE_OK) {
        NSLog(@"数据库打开成功");
    }else{
        NSLog(@"数据库打开失败");
    }
}

//创建表单
-(void)createTable
{
    //创建SQL语句
    NSString *createSQL = @"CREATE TABLE IF NOT EXISTS collect(number integer PRIMARY KEY AUTOINCREMENT,  title TEXT, urlid TEXT ,image TEXT ,time TEXT,url TEXT)";
    
    //核心API
    int result = sqlite3_exec(db, createSQL.UTF8String, NULL, NULL, nil);
    
    if (SQLITE_OK == result) {
        NSLog(@"创建表格成功");
    }else{
        NSLog(@"创建表格失败");
    }
}

//插入数据（学生信息）
-(void)insertCollect:(Collection *)collect


{
    //创建SQL语句
    NSString *insertSQL = [NSString stringWithFormat:@"INSERT INTO collect(title,urlid,image,time,url) VALUES('%@','%@','%@','%@','%@')",collect.title, collect.urlid,collect.image,collect.time,collect.url];
    
    //核心API
    int result = sqlite3_exec(db, insertSQL.UTF8String, NULL, NULL, nil);
    
    
    if (SQLITE_OK == result) {
        NSLog(@"插入数据成功");
    }else{
        NSLog(@"插入数据失败");
       
    }
}


//删除数据
-(void)deleteCollect:(NSString *)title
{
    NSString *deleteSQL = [NSString stringWithFormat:@"DELETE FROM collect WHERE title = '%@'",title];
    int result = sqlite3_exec(db, deleteSQL.UTF8String, NULL, NULL, nil);
    if (SQLITE_OK == result) {
        NSLog(@"删除成功");
    }else{
        NSLog(@"删除失败");
    }
    
}
//关闭数据库
-(void)close
{
    int result = sqlite3_close(db);
    if (SQLITE_OK == result) {
        db = nil;
        NSLog(@"关闭数据库成功");
    }else{
        NSLog(@"关闭数据库失败");
    }
}

//查询
-(NSArray *)selectCollect

{
    //创建数组
    NSMutableArray *arr = [NSMutableArray array];
    
    //创建SQL查询语句
    NSString *selectSQL = @"select * from collect";
    
    //准备好的SQL语句对象
    sqlite3_stmt *stmt = nil;
    
    //
    int result = sqlite3_prepare_v2(db, selectSQL.UTF8String, -1, &stmt, nil);
    if (SQLITE_OK == result) {
        NSLog(@"查询成功");
        
        //        //执行准备好的SQL语句
        //        sqlite3_step(stmt);
        //sqlite3_step(stmt); 这个函数的返回值为 SQLITE_ROW
        
        //循环 判断到什么时候结束
        while (SQLITE_ROW == sqlite3_step(stmt)) {
            
            //但需要对象查询之后的结果进行处理操作时，使用sqlite3_column_ 函数
            const unsigned char *title = sqlite3_column_text(stmt, 1);//取出name
            const unsigned char *urlid = sqlite3_column_text(stmt, 2);
            const unsigned char *image = sqlite3_column_text(stmt, 3);
            const unsigned char *time  = sqlite3_column_text(stmt, 4);
            const unsigned char *url   = sqlite3_column_text(stmt, 5);
            //创建Model对象，赋值
            Collection  *collect = [[Collection  alloc] init];
            
            collect.title = [NSString stringWithUTF8String:(const char *)title];
            collect.urlid = [NSString stringWithUTF8String:(const char *)urlid];
            
            collect.image = [NSString stringWithUTF8String:(const char *)image];
            collect.time  = [NSString stringWithUTF8String:(const char *)time];
            collect.url   = [NSString stringWithUTF8String:(const char *)url];
            
            //model 对象添加到数组中
            [arr addObject:collect];
        }
        //销毁 准备好的SQL语句对象
        sqlite3_finalize(stmt);
    }else{
        
        NSLog(@"查询失败");
        
        sqlite3_finalize(stmt);
    }
    
    return arr;
}

@end
